import pandas as pd 
import numpy as np
import time
import cx_Oracle
from sqlalchemy import create_engine




#标记命中策略企业 返回策略表
def rule_record(df):
    df_rule_record = df.copy()
    #E
    cond1 = df_rule_record.hjxypjdj.isin(['D','4',4.0,4,'4.0'])
    cond2 = df_rule_record.hj_cflx.isin(['5',5.0,5,'5.0'])
    cond3 = df_rule_record.sthjyzsx == 'Y'
    #S
    cond4 = df_rule_record.aqsc_cflx.isin(['5',5.0,5,'5.0'])
    cond5 = df_rule_record.ggxypjjg.astype('float') < 700
    #g
    cond6 = df_rule_record.swzdwfhmd == 'Y'
    cond7 = df_rule_record.flbzxr_lrsxbs == 'Y'
    cond8 = df_rule_record.xzcf_cflx.isin(['5',5.0,5,'5.0'])

    for i in range(1,9):
        cond = 'cond' + str(i)
        rule = 'rule' + str(i)
        df_rule_record.loc[eval(cond),rule] = 1

    lst = ['rating_id','rule1','rule2','rule3','rule4','rule5','rule6','rule7','rule8']
    df_rule_result = df_rule_record[lst].set_index('rating_id').stack().reset_index()
    df_rule_result.columns = ['rating_id','rule_code','if_rule']
    df_rule_result['esg_time'] = time.strftime('%Y/%m/%d %H:%M:%S',time.localtime())    
    del df_rule_result['if_rule']
    return df_rule_result
            
       
#策略查找函数
def find_rule(df):
    dfr = df.copy()
    dfr.loc[(dfr.hjxypjdj.isin(['D','4',4.0,4,'4.0'])),'e_flag'] = 1
    dfr.loc[(dfr.hj_cflx.isin(['5',5.0,5,'5.0'])),'e_flag'] = 1
    dfr.loc[(dfr.sthjyzsx == 'Y'),'e_flag'] = 1
    dfr.e_flag.fillna(0,inplace=True)
    
    dfr.loc[(dfr.aqsc_cflx.isin(['5',5.0,5,'5.0'])),'s_flag'] = 1
    dfr.loc[(dfr.ggxypjjg.astype('float') < 700),'s_flag'] = 1
    dfr.s_flag.fillna(0,inplace=True)
    
    dfr.loc[(dfr.swzdwfhmd == 'Y'),'g_flag'] = 1
    dfr.loc[(dfr.flbzxr_lrsxbs == 'Y'),'g_flag'] = 1
    dfr.loc[(dfr.xzcf_cflx.isin(['5',5.0,5,'5.0'])),'g_flag'] = 1
    dfr.g_flag.fillna(0,inplace=True)
    return dfr[['rating_id','id_number','e_flag','s_flag','g_flag']]





#一票否决函数
def one_kill(dfs,dfr):
    #一票否决
    df_tmp = dfs.copy()
    data = pd.merge(df_tmp,dfr[['rating_id','e_flag','s_flag','g_flag']],how='left',on = 'rating_id')
    data.loc[data.e_flag == 1,'e_score'] = 0
    data.loc[data.s_flag == 1,'s_score'] = 0
    data.loc[data.g_flag == 1,'g_score'] = 0
    data['esg_score'] = data[['e_score','s_score','g_score']].sum(axis=1)
    data.drop(['e_flag','s_flag','g_flag'],axis=1,inplace=True)
    return data
    

